"""
@创作时间   : 2021/8/22 22:07
@作者      : 何启哲
@文件名    : sql.py
@工具      : PyCharm
@用处      :操作数据库
"""
import pymysql
db = pymysql.connect(host="localhost",
                     user="root",
                     password="asdfjkl;",
                     # database="student",
                     cursorclass=pymysql.cursors.DictCursor,
                     charset="utf8mb4")
cur = db.cursor()
# 防止新用户没有数据库
cur.execute("CREATE DATABASE IF NOT EXISTS student DEFAULT CHARACTER SET utf8")

db = pymysql.connect(host="localhost",
                     user="root",
                     password="asdfjkl;",
                     database="student",
                     cursorclass=pymysql.cursors.DictCursor,
                     charset="utf8mb4")
cur = db.cursor()


def creat_default_user():
    try:

        sql = """
        CREATE TABLE IF NOT EXISTS score1 (
            id int(8) NOT NULL AUTO_INCREMENT,
            students varchar(25) NOT NULL,
            score int(255) NOT NULL,
            score1 int(255) NOT NULL,
            PRIMARY KEY (id)
        ) ENGINE=MyISAM CHARACTER SET utf8;
        """

        cur.execute(sql)
        sql = """
        CREATE TABLE IF NOT EXISTS user1 (
            password varchar(255),
            grade int(255) NOT NULL,                  #用户所属年集
            class int(255) NOT NULL                  #用户所属班级
        ) ENGINE=MyISAM  DEFAULT CHARACTER  SET utf8;
        """
        cur.execute(sql)
    except Exception as e:
        db.rollback()
        print(f"出错了！{e}")


def get_right(user):

    sql = """
        SELECT password FROM %s
    """ % user
    cur.execute(sql)
    rpwd = cur.fetchone()
    return rpwd


db.close()
